using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;
using RBAC.Read.Api.Application.Dto.ERP.SalesManagement.H;
using RBAC.Interstructrue.Base;
using static RBAC.ErrorCode.APIEnums;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 获取发货通知单列表处理器
    /// </summary>
    public class GetDeliveryNoticeListHandler : IRequestHandler<GetDeliveryNoticeListCommand, APIResult<GetDeliveryNoticeListDto>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public GetDeliveryNoticeListHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<APIResult<GetDeliveryNoticeListDto>> Handle(GetDeliveryNoticeListCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var connection = _dbConnectionFactory.CreateConnection();
                
                // 构建查询条件
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();
                
                // 基础条件：只查询未删除的记录
                whereConditions.Add("dn.IsDeleted = 0");
                
                if (!string.IsNullOrEmpty(request.SalesOrderCode))
                {
                    whereConditions.Add("dn.Sales_order_code LIKE @SalesOrderCode");
                    parameters.Add("@SalesOrderCode", $"%{request.SalesOrderCode}%");
                }
                
                if (!string.IsNullOrEmpty(request.BillCode))
                {
                    whereConditions.Add("dn.Bill_code LIKE @BillCode");
                    parameters.Add("@BillCode", $"%{request.BillCode}%");
                }
                
                if (!string.IsNullOrEmpty(request.CustomerName))
                {
                    whereConditions.Add("c.CustomerName LIKE @CustomerName");
                    parameters.Add("@CustomerName", $"%{request.CustomerName}%");
                }
                
                if (!string.IsNullOrEmpty(request.SalesmanName))
                {
                    whereConditions.Add("u.UserName LIKE @SalesmanName");
                    parameters.Add("@SalesmanName", $"%{request.SalesmanName}%");
                }

                // 单据状态筛选（0=开立，1=审核，2=退回）
                if (request.Status.HasValue)
                {
                    whereConditions.Add("dn.Status = @Status");
                    parameters.Add("@Status", request.Status.Value);
                }

                // 业务类型筛选（如 普通发货、直运发货）
                if (!string.IsNullOrWhiteSpace(request.BizType))
                {
                    whereConditions.Add("dn.Biz_type = @BizType");
                    parameters.Add("@BizType", request.BizType);
                }

                if (!string.IsNullOrEmpty(request.MaterialName))
                {
                    whereConditions.Add("m.MaterName LIKE @MaterialName");
                    parameters.Add("@MaterialName", $"%{request.MaterialName}%");
                }
                
                if (request.StartDate.HasValue)
                {
                    whereConditions.Add("dn.Bill_date >= @StartDate");
                    parameters.Add("@StartDate", request.StartDate.Value.Date);
                }
                
                if (request.EndDate.HasValue)
                {
                    whereConditions.Add("dn.Bill_date <= @EndDate");
                    parameters.Add("@EndDate", request.EndDate.Value.Date.AddDays(1).AddMilliseconds(-1));
                }

                var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // 查询总数
                var countSql = $@"
                    SELECT COUNT(DISTINCT dn.Id)
                    FROM Delivery_notice dn
                    LEFT JOIN Customer c ON dn.Customer_id = c.Id
                    LEFT JOIN User u ON dn.Salesman_id = u.Id
                    LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND dni.IsDeleted = 0
                    LEFT JOIN Mater m ON dni.Material_id = m.Id
                    {whereClause}";

                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                // 分页查询数据
                var offset = (request.PageIndex - 1) * request.PageSize;
                var dataSql = $@"
                    SELECT DISTINCT
                        (@row_number:=@row_number + 1) as SerialNumber,
                        dn.Id,
                        dn.Bill_code as BillCode,
                        dn.Bill_date as BillDate,
                        dn.Status,
                        CASE dn.Status
                            WHEN 0 THEN '开立'
                            WHEN 1 THEN '审核'
                            WHEN 2 THEN '退回'
                            ELSE '未知'
                        END as StatusDescription,
                        dn.Biz_type as BizType,
                        dn.Sales_order_code as SalesOrderCode,
                        dn.Customer_id as CustomerId,
                        c.CustomerName,
                        dn.Salesman_id as SalesmanId,
                        u.UserName as SalesmanName,
                        dn.Warehouse_id as WarehouseId,
                        w.WarehouseName,
                        dn.Receiver,
                        dn.Phone,
                        dn.Address,
                        dn.Delivery_date as DeliveryDate,
                        dn.Remark,
                        dn.Freight,
                        dn.Loading_fee as LoadingFee,
                        dn.CreateTime,
                        dn.CreateUser,
                        '查看 编辑 删除' as Operation
                    FROM Delivery_notice dn
                    LEFT JOIN Customer c ON dn.Customer_id = c.Id
                    LEFT JOIN User u ON dn.Salesman_id = u.Id
                    LEFT JOIN WarehouseList w ON dn.Warehouse_id = w.Id
                    LEFT JOIN User au ON dn.Audit_user_id = au.Id
                    LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND dni.IsDeleted = 0
                    LEFT JOIN Mater m ON dni.Material_id = m.Id
                    CROSS JOIN (SELECT @row_number := {offset}) as r
                    {whereClause}
                    ORDER BY dn.Bill_date DESC, dn.Id DESC
                    LIMIT @PageSize OFFSET @Offset";

                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", request.PageSize);

                var items = await connection.QueryAsync<DeliveryNoticeListDto>(dataSql, parameters);

                var result = new GetDeliveryNoticeListDto
                {
                    TotalCount = totalCount,
                    Items = items.ToList()
                };

                return new APIResult<GetDeliveryNoticeListDto>
                {
                    Code = Success,
                    Msg = "查询成功",
                    Data = result
                };
            }
            catch (Exception ex)
            {
                return new APIResult<GetDeliveryNoticeListDto>
                {
                    Code = Error,
                    Msg = $"查询发货通知单列表失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
} 